package com.blank.web.dao;

import com.blank.web.bean.ClassBean;
import com.blank.web.utils.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ClassDaoImp implements ClassDao {
    @Override
    public List<ClassBean> findAllClass() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //获取连接
        try {
            connection = DbUtil.getConnection();
            //编写sql语句
            String sql = "select * from t_class";

           preparedStatement = connection.prepareStatement(sql);

           resultSet = preparedStatement.executeQuery();

            List<ClassBean> classBeanList = new ArrayList<ClassBean>();

            while(resultSet.next()){
                ClassBean classBean = new ClassBean(resultSet.getInt("cid"),resultSet.getInt("cGrade"),
                        resultSet.getString("teacherName"),resultSet.getString("slogan"),resultSet.getInt("classNum"));

                classBeanList.add(classBean);
            }
            return classBeanList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
                if(null != resultSet){
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public void addClass(ClassBean classBean) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        //获取数据库的连接
        try {
            connection = DbUtil.getConnection();
            //sql语句
            String sql = "insert into t_class (cGrade,teacherName,slogan,classNum) values (?,?,?,?)";
            //执行sql并返回相应的结果
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,classBean.getGrade());
            preparedStatement.setString(2,classBean.getTeacherName());
            preparedStatement.setString(3,classBean.getSlogan());
            preparedStatement.setInt(4,classBean.getClassNum());

            int i = preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("插入数据成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


    }

    @Override
    public void DelClass(int cid) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        //1、获取连接
        try {
             connection = DbUtil.getConnection();
            //编写sql语句
            String sql = "delete from t_class where cid = ?";
            //执行sql语句并返回结果
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,cid);
            int i = preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public ClassBean findClassByCid(int cid) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //获取连接
        try {
             connection = DbUtil.getConnection();
            //sql语句
            String sql = "select * from t_class where cid = ?";
            //执行sql语句
             preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,cid);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                ClassBean classBean = new ClassBean();
                classBean.setCid(resultSet.getInt("cid"));
                classBean.setGrade(resultSet.getInt("cGrade"));
                classBean.setTeacherName(resultSet.getString("teacherName"));
                classBean.setSlogan(resultSet.getString("slogan"));

                System.out.println(classBean);
                return classBean;
            }
            System.out.println("查找成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
                if(null != resultSet){
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public void UpdateClass(ClassBean classBean) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        //获取数据库的连接
        try {
            connection = DbUtil.getConnection();
            //sql语句
            String sql = "update t_class set cgrade = ?,teacherName = ?,slogan = ?,classNum = ? where cid = ?";

            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setInt(1,classBean.getGrade());
            preparedStatement.setString(2,classBean.getTeacherName());
            preparedStatement.setString(3,classBean.getSlogan());
            preparedStatement.setInt(4,classBean.getClassNum());
            preparedStatement.setInt(5,classBean.getCid());

            int i = preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("更新成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<ClassBean> searchClassByName(String name) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
             connection = DbUtil.getConnection();

            String sql = "select * from t_class where teacherName like ?";

            List<ClassBean> list = new ArrayList<ClassBean>();

             preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1,"%"+name+"%");

            resultSet = preparedStatement.executeQuery();

            while(resultSet.next()){
                ClassBean classBean = new ClassBean();
                classBean.setCid(resultSet.getInt("cid"));
                classBean.setTeacherName(resultSet.getString("teacherName"));
                classBean.setGrade(resultSet.getInt("cGrade"));
                classBean.setSlogan(resultSet.getString("slogan"));
                classBean.setClassNum(resultSet.getInt("classNum"));

                list.add(classBean);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
                if(null != resultSet){
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public List<ClassBean> findClassByPage(int currentPage) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DbUtil.getConnection();

            String sql = "select * from t_class limit ? offset ?";

             preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setInt(1,PAGE_SIZE);
            preparedStatement.setInt(2,(currentPage-1)*PAGE_SIZE);
            resultSet = preparedStatement.executeQuery();
            List<ClassBean> list = new ArrayList<ClassBean>();
            while (resultSet.next()){
                ClassBean classBean = new ClassBean();
                classBean.setCid(resultSet.getInt("cid"));
                classBean.setTeacherName(resultSet.getString("teacherName"));
                classBean.setGrade(resultSet.getInt("cGrade"));
                classBean.setSlogan(resultSet.getString("slogan"));
                classBean.setClassNum(resultSet.getInt("classNum"));

                list.add(classBean);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
                if(null != resultSet){
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;

    }

    @Override
    public int findCount() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DbUtil.getConnection();

            String sql = "select count(*) from t_class";

             preparedStatement = connection.prepareStatement(sql);

             resultSet = preparedStatement.executeQuery();

            if(resultSet.next()){
                int i = resultSet.getInt("count(*)");
                System.out.println(i);
                return i;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
                if (null != resultSet){
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public int judgeContainStudent(int cid) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //获取连接
        try {
            connection = DbUtil.getConnection();

            String sql = "SELECT count(*) FROM t_students WHERE class_id = ?";

            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setInt(1,cid);

            resultSet = preparedStatement.executeQuery();

            if(resultSet.next()){
                int i = resultSet.getInt("count(*)");
                return i;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DbUtil.closeResource(connection,preparedStatement);
                if (null != resultSet){
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return 0;
    }
}
